package com.CTAtracker.android.DAO;

import java.util.ArrayList;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

import com.CTAtracker.android.DataBase.MySQLiteHelperStation;
import com.CTAtracker.android.Models.AroundStation;
import com.CTAtracker.android.Models.ColorStation;
import com.CTAtracker.android.Models.Position;
import com.CTAtracker.android.Models.Station;

/**
 * Class that retrieve the data of the table Station from the database
 *
 */
public class StationDataSource {
	/**
	 * The SQLite database
	 */
	private SQLiteDatabase database;
	/**
	 * The helper to the table Station in the database
	 */
	private MySQLiteHelperStation dbHelperStation;

	/**
	 * Constructor, create the database helper from the contaxt
	 * @param context
	 */
	public StationDataSource(Context context) {
		dbHelperStation = new MySQLiteHelperStation(context);
	}

	/**
	 * Open the database to be writable
	 * @throws SQLException fail to open the database
	 */
	public void open() throws SQLException {
		database = dbHelperStation.getWritableDatabase();
	}

	/**
	 * Close the connection to the database
	 */
	public void close() {
		dbHelperStation.close();
	}

	/**
	 * Look up for station matching the parameter name
	 * @param name the name we want to match 
	 * @return a list of station with their name matching the parameter name
	 */
	public ArrayList<Station> getStationMatches(String name) {
		ArrayList<Station> stations = new ArrayList<Station>();
		Cursor cursor = database.query(
				MySQLiteHelperStation.TABLE_STATION, 
				MySQLiteHelperStation.columnsStation, 
				MySQLiteHelperStation.STATION_NAME + " LIKE '%" + name + "%'", 
				null, 
				MySQLiteHelperStation.STATION_PARENT_STOP_ID,
				null, 
				null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			Station station = cursorToListStation(cursor);
			stations.add(station);
			cursor.moveToNext();
		}
		cursor.close();
		return stations;
	}

	/**
	 * Get a station from the database with the 2 parameters
	 * @param idStation Id of the station 
	 * @param lineId Id of the line
	 * @return the station
	 */
	public Station getStationByID(int idStation, int lineId){
		Cursor cursor = database.query(
				MySQLiteHelperStation.TABLE_STATION, 
				MySQLiteHelperStation.allColumns, 
				MySQLiteHelperStation.STATION_ID + "=" + String.valueOf(idStation) 
				+ " AND " + MySQLiteHelperStation.STATION_LINE_ID + "=" + lineId, 
				null, null, null, null);
		cursor.moveToFirst();
		Station station = cursorToStation(cursor);
		cursor.close();
		return station;
	}

	/**
	 * Get a station from the database with the 2 parameters 
	 * @param idStopStation Id of the stop 
	 * @param lineId Id of the line
	 * @return the station
	 */
	public Station getStationByStopID(int idStopStation, int lineId){
		Cursor cursor = database.query(
				MySQLiteHelperStation.TABLE_STATION, 
				MySQLiteHelperStation.allColumns, 
				MySQLiteHelperStation.STATION_STOP_ID + "=" + String.valueOf(idStopStation) 
				+ " AND " + MySQLiteHelperStation.STATION_LINE_ID + "=" + lineId, 
				null, null, null, null);
		cursor.moveToFirst();
		Station station = cursorToStation(cursor);
		cursor.close();
		return station;
	}

	/**
	 * Get a list of station from the database with the 2 parameters 
	 * @param parentId the Id of the parent station
	 * @param lineId the Id of the line
	 * @return the list of station 
	 */
	public ArrayList<Station> getStationByParentId(int parentId, int lineId) {
		ArrayList<Station> stations = new ArrayList<Station>();
		Cursor cursor = database.query(
				MySQLiteHelperStation.TABLE_STATION, 
				MySQLiteHelperStation.allColumns, 
				MySQLiteHelperStation.STATION_PARENT_STOP_ID + "=" + String.valueOf(parentId)
				+ " AND " + MySQLiteHelperStation.STATION_LINE_ID + "=" + String.valueOf(lineId), 
				null, null, null, null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			Station station = cursorToStation(cursor);
			stations.add(station);
			cursor.moveToNext();
		}
		cursor.close();
		return stations;
	}
	
	public ArrayList<Station> getStationByParentId(int parentId) {
		ArrayList<Station> stations = new ArrayList<Station>();
		Cursor cursor = database.query(
				MySQLiteHelperStation.TABLE_STATION, 
				MySQLiteHelperStation.allColumns, 
				MySQLiteHelperStation.STATION_PARENT_STOP_ID + "=" + String.valueOf(parentId), 
				null, MySQLiteHelperStation.STATION_LINE_ID, null, null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			Station station = cursorToStation(cursor);
			stations.add(station);
			cursor.moveToNext();
		}
		cursor.close();
		return stations;
	}

	/**
	 * Get a list of station from the database with the parameter
	 * @param lineId the id line
	 * @return The array list of station
	 */
	public ArrayList<Station> getStation(int lineId) {
		ArrayList<Station> stations = new ArrayList<Station>();

		Cursor cursor = database.query(
				MySQLiteHelperStation.TABLE_STATION,
				MySQLiteHelperStation.columnsStation, 
				MySQLiteHelperStation.STATION_LINE_ID + "=" + String.valueOf(lineId), 
				null, 
				MySQLiteHelperStation.STATION_PARENT_STOP_ID,
				null, 
				MySQLiteHelperStation.STATION_STOP_NAME);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			Station station = cursorToListStation(cursor);
			stations.add(station);
			cursor.moveToNext();
		}
		cursor.close();
		return stations;
	}

	/**
	 * Get a list of StopId from the database with the 2 parameters	
	 * @param parentStopId the Id of the parent stop
	 * @param lineId the Id of the lne
	 * @return the array list of stopId
	 */
	public ArrayList<Integer> getListStopId(int parentStopId,
			int lineId) {
		ArrayList<Integer> listStopId = new ArrayList<Integer>();
		Cursor cursor = database.query(MySQLiteHelperStation.TABLE_STATION,
				MySQLiteHelperStation.columnIdStop, 
				MySQLiteHelperStation.STATION_PARENT_STOP_ID + "="+ String.valueOf(parentStopId)
				+ " and " + MySQLiteHelperStation.STATION_LINE_ID + "=" + String.valueOf(lineId),
				null, null, null, null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			listStopId.add(cursor.getInt(0));
			cursor.moveToNext();
		}
		cursor.close();
		return listStopId;
	}

	/**
	 * Get the list of all the stations around
	 * @return
	 */
	public ArrayList<AroundStation> getAllStationAround() {
		ArrayList<AroundStation> stations = new ArrayList<AroundStation>();

		Cursor cursor = database.query(
				MySQLiteHelperStation.TABLE_STATION,
				MySQLiteHelperStation.columnsStation, 
				null, 
				null, 
				MySQLiteHelperStation.STATION_PARENT_STOP_ID,
				null, 
				null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			AroundStation aroundStation = new AroundStation(cursorToListStation(cursor));
			stations.add(aroundStation);
			cursor.moveToNext();
		}
		cursor.close();
		return stations;
	}
	
	public boolean isUnique(int parent_stop_id) {

		Cursor cursor = database.query(
				MySQLiteHelperStation.TABLE_STATION,
				MySQLiteHelperStation.columnsStation, 
				MySQLiteHelperStation.STATION_PARENT_STOP_ID + "="+ String.valueOf(parent_stop_id), 
				null, 
				null,
				null, 
				null);
		cursor.moveToFirst();
		int count = cursor.getCount();
		cursor.close();
		return count <=2;
	}
	
	public ArrayList<Station> getListStationByLine(int idLine) {
		ArrayList<Station> stations = new ArrayList<Station>();

		Cursor cursor = database.query(
				MySQLiteHelperStation.TABLE_STATION,
				MySQLiteHelperStation.columnsStation, 
				MySQLiteHelperStation.STATION_LINE_ID + "="+String.valueOf(idLine), 
				null, 
				MySQLiteHelperStation.STATION_PARENT_STOP_ID,
				null, 
				MySQLiteHelperStation.STATION_SORT);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			Station station = cursorToListStation(cursor);
			stations.add(station);
			cursor.moveToNext();
		}
		cursor.close();
		return stations;
	}
	
	/**
	 * Get the list of all the stations
	 * @return
	 */
	public ArrayList<Station> getAllStation() {
		ArrayList<Station> stations = new ArrayList<Station>();

		Cursor cursor = database.query(
				MySQLiteHelperStation.TABLE_STATION,
				MySQLiteHelperStation.columnsStation, 
				null, 
				null, 
				MySQLiteHelperStation.STATION_PARENT_STOP_ID,
				null, 
				null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			Station station = cursorToListStation(cursor);
			stations.add(station);
			cursor.moveToNext();
		}
		cursor.close();
		return stations;
	}
	
	public ArrayList<ColorStation> getListColorStation(int parentStopId) {
		ArrayList<ColorStation> listIdLine = new ArrayList<ColorStation>();
		Cursor cursor = database.query(MySQLiteHelperStation.TABLE_STATION,
				MySQLiteHelperStation.columnIdLine, 
				MySQLiteHelperStation.STATION_PARENT_STOP_ID + "="+ String.valueOf(parentStopId),
				null, null, null, null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			listIdLine.add(new ColorStation(cursor.getInt(0)-1));
			cursor.moveToNext();
		}
		cursor.close();
		return listIdLine;
	}
	
	/**
	 * Return a station from the cursor
	 * The instantiated value of the station are : 
	 * StationId, name, longitude, latitude, 
	 * ADA, parent_stop_id, line_id  
	 * @param cursor the cursor return by the database 
	 * @return a new station
	 */
	private Station cursorToListStation(Cursor cursor) {
		Station station = new Station();

		station.setStationId(cursor.getInt(0));
		station.set_name(cursor.getString(1));
		station.set_position(new Position(cursor.getFloat(3), cursor.getFloat(2)));
		station.set_ADA(cursor.getInt(4));
		station.set_parent_stop_id(cursor.getInt(5));
		station.set_line_id(cursor.getInt(6));
		station.set_color(new ColorStation(cursor.getInt(6)-1));

		return station;
	}

	/**
	 * Return a station from the cursor
	 * The instantiated all the values of the station
	 * @param cursor the cursor return by the database 
	 * @return a new station
	 */
	private Station cursorToStation(Cursor cursor) {
		Station station = new Station();

		station.setStationId(cursor.getInt(0));
		station.set_name(cursor.getString(1));
		station.set_stop_id(cursor.getInt(2));
		station.set_stop_name(cursor.getString(3));
		station.set_direction(cursor.getString(4));
		station.set_position(new Position(cursor.getFloat(6), cursor.getFloat(5)));
		station.set_ADA(cursor.getInt(7));
		station.set_parent_stop_id(cursor.getInt(8));
		station.set_line_id(cursor.getInt(9));
		station.set_color(new ColorStation(cursor.getInt(9)-1));

		return station;
	}


}
